﻿using System;
using System.Collections.Generic;
using ExcelDna.Integration;
using System.Linq;
using System.Text;
using System.Threading;

namespace ExcelDnaDemo
{
    /// <summary>
    /// ExcelDNA 自定义函数
    /// </summary>
    public static class ExcelDnaUDF
    {
        #region XLL的自定义函数
        [ExcelFunction(Description = "将输入文本转为UTF8编码的Base64文本",
                       Category = "ExcelDNADemo for WPS个人版",
                       IsHidden = false, IsVolatile = false, IsMacroType = false, IsThreadSafe = false,
                       Name = "Base64UTF8")]
        public static object EncodeBase64UTF8([ExcelArgument(Description = "输入的文本", Name = "InputText")] object source)
        {
            if (source is ExcelDna.Integration.ExcelMissing) return ExcelError.ExcelErrorNA;
            string text = source.ToString();
            if (String.IsNullOrEmpty(text)) return ExcelError.ExcelErrorNull;
            byte[] bytes = Encoding.UTF8.GetBytes(text);
            return Convert.ToBase64String(bytes);
        }         
        
        [ExcelFunction(Description = "Rewrite Caller。在WPS下执行会出现错误。",
                       Category = "ExcelDNADemo for WPS个人版",
                       IsHidden = false, IsVolatile = false, IsMacroType = true,  
                       Name = "RewriteCaller")]
        public static object asyncRewriteCaller()
        {
            ExcelReference caller = (ExcelReference)XlCall.Excel(XlCall.xlfCaller);
            if (caller is ExcelReference)
            {
                ExcelAsyncUtil.QueueAsMacro(
                delegate
                {
                    //Unexpected return value from Application.Run("SyncMacro_...") 
                    caller.SetValue("No formula here anymore...");
                    
                });

                return DateTime.Now.ToShortTimeString();
            }
            return "wrong caller";
        }

        [ExcelFunction(Description = "延迟指定的毫秒数，返回当前时间", Category = "ExcelDNADemo for WPS个人版",
                       IsHidden = false, IsMacroType = true, Name = "SleepAsync")]
        public static object SleepAsync(int ms)
        {
            return ExcelAsyncUtil.Run("SleepAsync", ms, delegate
            {
                Thread.Sleep(ms);
                return "Woke Up at " + DateTime.Now.ToString("1:HH:mm:ss.fff");
            });            
        }        

        /// <summary>
        /// Addition UDF function
        /// </summary>
        /// <param name="Param1"></param>
        /// <param name="Param2"></param>
        /// <param name="Param_range"></param>
        /// <returns></returns>
        [ExcelFunction(Description = "Addition function",
                       Category = "ExcelDNADemo for WPS个人版",
                       IsHidden = false, IsVolatile = true, IsMacroType = true,
                       Name = "AddThem")]
        public static object AddThem([ExcelArgument(Description = "DecimalValue1 or Range1", Name = "decimal1")] object Param1,
                                     [ExcelArgument(Description = "DecimalValue2 or Range2", Name = "decimal2")] object Param2,
                                     [ExcelArgument(Description = "Output to Range3", Name = "decimal_range", AllowReference = true)] object Param_range)
        {
            double value1, value2, value3;

            if (ExcelDnaUtil.IsInFunctionWizard()) return "Waiting for click on wizard ok button to calculate.";

            if (Param1 is ExcelDna.Integration.ExcelMissing) return ExcelError.ExcelErrorNA;
            if (Param2 is ExcelDna.Integration.ExcelMissing) return ExcelError.ExcelErrorNA;
            if (!(Param_range is ExcelReference)) return ExcelError.ExcelErrorRef;

            if (Param1 is ExcelDna.Integration.ExcelEmpty)
            {
                value1 = 0;
            }
            else
            {
                if (!IsNumberic(Param1.ToString(), out value1)) return (object)ExcelError.ExcelErrorValue;
            }
            if (Param2 is ExcelDna.Integration.ExcelEmpty)
            {
                value2 = 0;
            }
            else
            {
                if (!IsNumberic(Param2.ToString(), out value2)) return (object)ExcelError.ExcelErrorValue;
            }

            Param_range = (Param_range as ExcelReference).GetValue();
            if (Param_range is ExcelDna.Integration.ExcelEmpty)
            {
                value3 = 0;
            }
            else
            {
                if (!IsNumberic(Param_range.ToString(), out value3)) return (object)ExcelError.ExcelErrorValue;
            }

            return value1 + value2 + value3;
        }


        [ExcelFunction(Description = "这是个数组函数示例。使用方法：选择四个单元格（2*2），输入公式 =WriteTestArray()，按下ctrl+shift+enter",
                       Category = "ExcelDNADemo for WPS个人版",
                       IsHidden = false, IsVolatile = false, IsMacroType = true, IsThreadSafe = true,
                       Name = "WriteTestArray")]
        public static object[,] WriteTestArray()
        {
            //数组公式才能使用这个函数，这是个示例。
            //先选择单元格范围（2*2），然后在公式栏输入公式=WriteTestArray()，按下ctrl+shift+enter，即可看到输出四个单元格。
            try
            {
                return new object[2, 2] { { "one", "two" }, { "three", "four" } };
            }
            catch
            {
                return new object[,] { { ExcelError.ExcelErrorValue } };
            }
        }

        public static object CallerAddress()
        {
            ExcelReference caller = (ExcelReference)XlCall.Excel(XlCall.xlfCaller);
            string refText = (string)XlCall.Excel(XlCall.xlfReftext, caller, true);          //limit 247bytes
            dynamic app = ExcelDnaUtil.Application;
            dynamic Range = app.Range[refText];
            return Range.Address;
        }
        #endregion XLL的自定义函数

        private static bool IsNumberic(string str,out double vsNum)
        {            
            bool isNum;
            isNum = double.TryParse(str, System.Globalization.NumberStyles.Float,
                System.Globalization.NumberFormatInfo.InvariantInfo, out vsNum);
            return isNum;
        }
        /// <summary>
        /// ExcelReference to Range
        /// </summary>
        /// <param name="xlref"></param>
        /// <returns></returns>
        private static dynamic ReferenceToRange(ExcelReference xlref)          //简版的
        {
            dynamic app = ExcelDnaUtil.Application;
            return app.Range[XlCall.Excel(XlCall.xlfReftext, xlref, true)];
        }
    }

    // RAII helper to deal with Excel selections
    public class ExcelSelectionHelper : XlCall, IDisposable
    {
        object oldScreenUpdating;
        object oldSelectionOnActiveSheet;
        object oldActiveCellOnActiveSheet;

        object oldSelectionOnRefSheet;
        object oldActiveCellOnRefSheet;

        public ExcelSelectionHelper(ExcelReference refToSelect)
        {
            oldScreenUpdating = Excel(xlfGetWorkspace, 40);
            Excel(xlcEcho, false);

            // Remember old selection state on the active sheet
            oldSelectionOnActiveSheet = Excel(xlfSelection);
            oldActiveCellOnActiveSheet = Excel(xlfActiveCell);

            // Switch to the sheet we want to select
            string refSheet = (string)Excel(xlSheetNm, refToSelect);
            Excel(xlcWorkbookSelect, new object[] { refSheet });

            // record selection and active cell on the sheet we want to select
            oldSelectionOnRefSheet = Excel(xlfSelection);
            oldActiveCellOnRefSheet = Excel(xlfActiveCell);

            // make the selection
            Excel(xlcFormulaGoto, refToSelect);
        }

        public void Dispose()
        {
            Excel(xlcSelect, oldSelectionOnRefSheet, oldActiveCellOnRefSheet);

            string oldActiveSheet = (string)Excel(xlSheetNm, oldSelectionOnActiveSheet);
            Excel(xlcWorkbookSelect, new object[] { oldActiveSheet });

            Excel(xlcSelect, oldSelectionOnActiveSheet, oldActiveCellOnActiveSheet);

            Excel(xlcEcho, oldScreenUpdating);
        }
    }
}
